"""
Case Type   : 数据库系统
Case Name   : 预编译sql为插入数据，执行预编译sql语句过程中修改表结构-删除字段
Create At   : 2021/10/12
Owner       : opentestcase024
Description :
    1.创建配置文件
    2.编译java文件
    3.运行java预编译代码（插入预编译重复执行）
    4.步骤3过程中，进行表结构变更-删除字段与预编译sql无关
    5.步骤3过程中，进行表结构变更-删除字段与预编译sql相关
    6.验证步骤3结果
Expect      :
    1.创建配置文件成功
    2.编译java文件成功
    3.运行java预编译代码（插入预编译重复执行），开始执行
    4.步骤3过程中，进行表结构变更-删除字段与预编译sql无关，修改成功
    5.步骤3过程中，进行表结构变更-删除字段与预编译sql相关，修改成功
    6.验证步骤3结果，由于步骤5操作中断
History     :
    Modified by opentestcase024 2023/11/17：增加日志打印，方便定位问题
"""
import os
import re
import time
import unittest

from testcase.utils.ComThread import ComThread
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class JDBCPrepareStatement(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.pri_root = Node(node='PrimaryRoot')
        self.log.info("----this is setup----")
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.target_path = os.path.join(macro.DB_BACKUP_PATH, "JDBC_test")
        self.properties = os.path.join(self.target_path,
                                       "jdbc_preparestat_case0002.properties")
        self.java_name = "jdbc_PrepareStatement_Case0001_05"
        self.tb_name = "tb_jdbc_PrepareStatement_Case0002"
        self.com = Common()
        self.constant = Constant()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.match_msg = 'ERROR: column ".*" of relation ".*" does not exist'

    def test_main(self):
        step_txt = '----查看参数默认值----'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param("log_statement")
        self.init_para2 = self.com.show_param("log_min_duration_statement")

        step_txt = '----step0:修改参数; expect:修改成功----'
        self.log.info(step_txt)
        msg = self.pri_sh.execute_gsguc('reload',
                                        self.constant.GSGUC_SUCCESS_MSG,
                                        f"log_statement=all",
                                        single=True)
        self.assertTrue(msg, '执行失败:' + step_txt)
        msg = self.pri_sh.execute_gsguc('reload',
                                        self.constant.GSGUC_SUCCESS_MSG,
                                        f"log_min_duration_statement=0",
                                        single=True)
        self.assertTrue(msg, '执行失败:' + step_txt)

        step_txt = '----step1:创建配置文件; expect:创建成功----'
        self.log.info(step_txt)
        self.com.scp_file(self.pri_root,
                          f"{self.java_name}.java", self.target_path)
        result = self.pri_root.sh(f"touch {self.properties}").result()
        self.log.info(result)
        config = f'echo "password=' \
            f'{self.pri_dbuser.db_password}"> {self.properties}'
        self.pri_root.sh(config)
        config = f'echo "port={self.pri_dbuser.db_port}">> ' \
            f'{self.properties}'
        self.pri_root.sh(config)
        config = f'echo "hostname={self.pri_dbuser.db_host}">> ' \
            f'{self.properties}'
        self.pri_root.sh(config)
        config = f'echo "user={self.pri_dbuser.db_user}">> ' \
            f'{self.properties}'
        self.pri_root.sh(config)
        config = f'echo "dbname={self.pri_dbuser.db_name}">> ' \
            f'{self.properties}'
        self.pri_root.sh(config)
        config = f'echo "tbname={self.tb_name}">> {self.properties}'
        self.pri_root.sh(config)
        config = f'cat {self.properties}'
        result = self.pri_root.sh(config).result()
        self.log.info(result)
        self.assertTrue("password=" in result and "port=" in result
                        and "hostname=" in result and "user=" in result
                        and "dbname=" in result and "tbname=" in result,
                        "执行失败" + step_txt)

        step_txt = '----step2:编译java文件; expect:编译成功----'
        self.log.info(step_txt)
        self.pri_root.scp_put(macro.JDBC_PATH,
                              f"{self.target_path}/postgresql.jar")
        cmd = f"javac -encoding utf-8 -cp " \
            f"{os.path.join(self.target_path, 'postgresql.jar')} " \
            f"{os.path.join(self.target_path, f'{self.java_name}.java')}"
        self.log.info(cmd)
        result = self.pri_root.sh(cmd).result()
        self.log.info(result)
        self.assertEqual('', result, "执行失败" + step_txt)

        step_txt = '----step3:运行java预编译代码（插入预编译重复执行）; expect:开始运行----'
        self.log.info(step_txt)
        java_cmd = f"java -cp " \
            f"{os.path.join(self.target_path, 'postgresql.jar')}" \
            f":{self.target_path} " \
            f"{self.java_name} -F {self.properties}"
        self.log.info(java_cmd)
        java_thread = ComThread(self.com.get_sh_result,
                                args=(self.pri_root, java_cmd))
        java_thread.setDaemon(True)
        java_thread.start()
        time.sleep(3)

        step_txt = '----step4:步骤3过程中，进行表结构变更-删除字段与预编译sql无关; expect:修改成功----'
        self.log.info(step_txt)
        alter_sql = f'alter table {self.tb_name} ' \
            f'DROP COLUMN c_customer_bak1;\d {self.tb_name}'
        result = self.pri_sh.execut_db_sql(alter_sql)
        self.log.info(result)

        check_sql = "select * from PG_STAT_ACTIVITY;"
        check_result = self.pri_sh.execut_db_sql(check_sql)
        self.log.info(check_result)

        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)

        step_txt = '----step5:步骤3过程中，进行表结构变更-删除字段与预编译sql相关; expect:修改成功----'
        self.log.info(step_txt)
        alter_sql = f'alter table {self.tb_name} ' \
            f'DROP COLUMN c_customer_bak;\d {self.tb_name}'
        result = self.pri_sh.execut_db_sql(alter_sql)
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      "执行失败" + step_txt)

        step_txt = '----step6:验证步骤3结果; expect:由于步骤5操作中断----'
        self.log.info(step_txt)
        java_thread.join(600)
        java_result = java_thread.get_result()
        result = len(re.findall(self.match_msg, java_result))
        self.assertEqual(1, result, "执行失败" + step_txt)
        suc_flag = "insert succeed!100000"
        self.assertNotIn(suc_flag, java_result, "执行失败" + step_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        self.log.info('----清理环境----')
        ps_cmd = f"ps ux |grep {self.java_name}|grep -v grep|" \
            f"tr -s ' '|cut -d ' ' -f 2"
        self.log.info(ps_cmd)
        ps_result = self.pri_root.sh(ps_cmd).result()
        self.log.info(ps_result)
        if ps_result != '':
            self.log.info('----清理java进程----')
            kill_cmd = f"ps ux |grep {self.java_name}|grep -v grep|" \
                f"tr -s ' '|cut -d ' ' -f 2|xargs kill -9"
            self.log.info(kill_cmd)
            self.pri_root.sh(kill_cmd)

        step1_txt = '----还原参数; expect:还原成功----'
        self.log.info(step1_txt)
        msg1 = self.pri_sh.execute_gsguc('reload',
                                         self.constant.GSGUC_SUCCESS_MSG,
                                         f"log_statement={self.init_para1}",
                                         single=True)

        msg2 = self.pri_sh.execute_gsguc('reload',
                                         self.constant.GSGUC_SUCCESS_MSG,
                                         f"log_min_duration_statement="
                                         f"{self.init_para2}",
                                         single=True)
        step2_txt = '----删除表; expect:删除成功----'
        self.log.info(step2_txt)
        cmd = f"drop table if exists {self.tb_name};"
        drop_result = self.pri_sh.execut_db_sql(cmd)
        self.log.info(drop_result)
        cmd = f"rm -rf {self.target_path}"
        self.log.info(cmd)
        self.pri_root.sh(cmd)

        self.assertTrue(msg1, '执行失败:' + step1_txt)
        self.assertTrue(msg2, '执行失败:' + step1_txt)
        self.assertIn(drop_result, self.constant.TABLE_DROP_SUCCESS,
                      '执行失败:' + step2_txt)

        self.log.info(f'----{os.path.basename(__file__)}:end----')
